import copy
import json
import logging
import os
import sys
import traceback
from datetime import datetime

import pymysql
import settings
from bot.insurance_planner_gpt.qw_server.utils import common_util
from common.log import logger
from bot.insurance_planner_gpt.config.conf import profile_


def sqlFactorReConnect(func):
    """
    跑sql装饰器，每次重连，跑完释放，避免长时间占据连接池。
    :param func:
    :return:
    """

    def myWrapper(self, *arg, **kvargs):  # 重新包装func，其参数列表与func一致
        ret = func(self, *arg, **kvargs)
        return ret

    return myWrapper  # 调用 myDecorator 能返回一个函数对象，用于给func重新赋值v


class Entity(object):

    def get_parameter_names(self):
        return list(self.__dict__.keys())

    def get_parameter_values(self, keys):
        values = []
        for key in keys:
            value = self.__dict__.get(key, None)
            if isinstance(value, int) or isinstance(value, float):
                values.append(str(value))
                continue

            if isinstance(value, str):
                value = value  # .encode('utf-8')
            if value == b'\x00':
                value = ""
            value = '"' + str(value) + '"'
            values.append(value)
        return values

    def __str__(self):
        return str(self.__dict__)


def entityDao(obj):
    """
    渲染对象装饰器。需要保证对象的属性与数据库字段名称保持一致。
    :param obj:
    :return:
    """

    def real_dec(func):
        def myWrapper(*arg, **kvargs):  # 重新包装func，其参数列表与func一致
            params = obj.get_parameter_names()
            original_data_list = func(params, *arg, **kvargs)
            obj_list = []
            for session in original_data_list:
                new_obj = copy.copy(obj)
                for ind, key in enumerate(params):
                    value = session[ind]
                    setattr(new_obj, key, value)
                obj_list.append(new_obj)
            return obj_list

        return myWrapper  # 调用 myDecorator 能返回一个函数对象，用于给func重新赋值v

    return real_dec


def sqlReConnect(func):
    """
    跑sql装饰器，每次重连，跑完释放，避免长时间占据连接池。
    :param func:
    :return:
    """

    def myWrapper(self, *arg, **kvargs):  # 重新包装func，其参数列表与func一致
        self._db_name = ''
        ret = func(self, *arg, **kvargs)
        return ret

    return myWrapper  # 调用 myDecorator 能返回一个函数对象，用于给func重新赋值v


class Mysqldb(object):

    def __init__(self):
        self._db_name = ""
        self._connect()
        pass

    def _connect(self):
        conf = settings.DBConfig

        try:
            self._conn = pymysql.connect(host=conf.HOST, user=conf.USERNAME,
                                         passwd=conf.PASSWORD, port=conf.PORT, charset='utf8mb4',
                                         autocommit=True
                                         )
            self._curDB = self._conn.cursor()

            self._conn.select_db(self._db_name or conf.DBNAME)
        except Exception as e:
            logging.error('can not connect to db ' + str(e))
            sys.exit(-1)

    def closedb(self):
        try:
            if self._conn:
                self._conn.close()
        except:
            logging.error('Can not close the db, maybe already closed.')

    def _run(self, sql, msg, is_commit):
        self._curDB.execute(sql)
        if is_commit:
            self._conn.commit()
        return self._curDB.fetchall()

    def run_sql(self, sql, msg, is_commit=False):
        try:
            return self._run(sql, msg, is_commit)
        except Exception as e:
            import traceback
            traceback.print_exc()
            logging.error(msg + " ERROR!")
            self._connect()
            try:
                return self._run(sql, msg, is_commit)
            except:
                import traceback
                traceback.print_exc()
                logging.error(msg + " ERROR!")
        return []


class EntitiesDao(Mysqldb):

    @sqlFactorReConnect
    def insert_ql_add_wechat_record(self, session_id, customer_id, robot_id, req_time, contact_id,
                                    tel, nick_name,company_id=None,case_id=None):
        """
        添加加微记录
        """
        result = ""
        insert_query = ""

        # company_id = 1
        # case_id = 1
        try:
            if session_id and not company_id :
                company_id, case_id = common_util.get_info_from_session_id(session_id)

            if not case_id:
                case_id = 1
            # 插入数据
            current_time = datetime.now()
            insert_query = f'''INSERT INTO ql_add_friend_record (company_id,customer_id,case_id,robot_id,req_time,contact_id,tel,nick_name,status,fail_reason,session_id,create_time,update_time)
                    VALUES ({company_id},'{customer_id}',{case_id},'{robot_id}','{req_time}','{contact_id}','{tel}','{nick_name}',0,'','{session_id}','{current_time}','{current_time}')'''
            logger.info(f"sql:{insert_query}")
            print(f"sql:{insert_query}")
            result = self.run_sql(insert_query, "insert")
        except Exception:
            logger.error(f"添加加微记录异常，session_id={session_id},sql:{insert_query},异常为：{traceback.format_exc()}")
        return result

    @sqlFactorReConnect
    def select_add_record_by_id(self, phone="", session_id=""):
        """
        查询加微记录
        """
        result = ""
        try:
            select_query = '''SELECT id,company_id,customer_id,case_id,add_way,robot_id,req_time,contact_id,tel,nick_name,alias_name,status,fail_reason,session_id,create_time,update_time FROM ql_add_friend_record where 
            1=1 '''
            if phone:
                select_query += f"and phone = {phone}"
            if session_id:
                select_query += f"and session_id = {session_id}"
            logger.info(f"sql:{select_query}")
            result = self.run_sql(select_query, "select")
        except Exception:
            logger.error(f"查询加微记录异常，phone={phone},session_id={session_id}")
        return result

    @sqlFactorReConnect
    def update_ql_add_wechat_record(self, phone, status, fail_reason, nick_name, contact_id):
        """
        修改加微记录
        """
        result = ""
        update_query = ""
        try:
            if not phone:
                return
            update_query = f'''UPDATE ql_add_friend_record set status={status},nick_name='{nick_name}',contact_id='{contact_id}',fail_reason='{fail_reason}' where tel = '{phone}' and status=0    
            '''
            logger.info(f"sql:{update_query}")
            result = self.run_sql(update_query, "update")
        except Exception:
            logger.error(f"更新加微记录异常，phone:{phone},sql:{update_query},异常是：{traceback.format_exc()}")
        return result

    @sqlFactorReConnect
    def insert_ql_add_wechat_result(self, session_id, customer_id, robot_id, contact_id, add_time, nick_name, add_way,company_id=None,case_id=None):
        """
        添加加微结果
        """
        result = ""
        insert_query = ""
        # company_id = 1
        # case_id = 1
        try:
            if session_id and not company_id:
                company_id, case_id = common_util.get_info_from_session_id(session_id)

            if not case_id:
                case_id = 1

            current_time = add_time
            if not add_time:
                current_time = datetime.now()
            # 插入数据
            insert_query = f'''INSERT INTO ql_add_friend_result (company_id,customer_id,case_id,contact_id,nick_name,robot_id,session_id,add_way,add_time,update_time,create_time)
                           VALUES ({company_id},'{customer_id}',{case_id},'{contact_id}','{nick_name}','{robot_id}','{session_id}',{add_way},'{current_time}','{current_time}','{current_time}')'''
            logger.info(insert_query)
            result = self.run_sql(insert_query, "insert")
        except Exception:
            logger.error(f"添加加微结果异常，session_id:{session_id},sql:{insert_query},异常为：{traceback.format_exc()}")
        return result

    @sqlFactorReConnect
    def insert_ql_contact_msg(self,msg):
        result = ""
        try:
            msg_con = msg.get('msg_content')
            msg_con = str(msg_con).replace("'", r"\'")

            insert_query = f'''INSERT INTO ql_work_wechat.ql_contact_msg
                        (receiver_id, sender_id, receive_time, msg_type, content, sender_type,robot_id,msg_id)
                        VALUES( '{msg.get('receiver_id')}', '{msg.get('sender_id')}', '{msg.get('receive_time')}', 
                        '{msg.get('msg_type')}', '{msg_con}', {msg.get('sender_type')},'{msg.get('robot_id')}','{msg.get('msg_id')}');
                        '''

            logger.info(insert_query)
            result = self.run_sql(insert_query, "insert")
        except Exception as ee:
            logger.error(traceback.format_exc())
        return result

    @sqlFactorReConnect
    def insert_ql_qw_contact_msg(self, msg):
        result = ""
        try:
            insert_query = f'''INSERT INTO ql_work_wechat.ql_qw_contact_msg
                           (session_id, role, message_id, attitude, msg_type, text_info,message_time,client_type)
                           VALUES( '{msg.get('session_id')}', '{msg.get('role')}', '{msg.get('message_id')}', 
                           '{msg.get('attitude')}', '{msg.get('msg_type')}', '{msg.get('text_info')}','{msg.get('message_time')}','{msg.get('client_type')}');
                           '''

            logger.info(insert_query)
            result = self.run_sql(insert_query, "insert")
        except Exception as ee:
            logger.error(traceback.format_exc())
        return result

    @sqlFactorReConnect
    def select_sop_list(self, sql):
        """
        查询加微记录
        """
        result = ""
        try:
            logger.info(f"sql:{sql}")
            result = self.run_sql(sql, "select")
        except Exception:
            logger.error(f" 查询SOP数据报错 sql:{sql}")
        return result

    @sqlFactorReConnect
    def insert_ql_sop_wechat_msg(self, day, request_time, contact_id, robot_id, uniq_sn, response_json, msg, send_time
                                 , url, expire_date, data_msg, msg_type):
        result = ""
        try:
            insert_query = f'''insert into ql_work_wechat.ql_sop_wechat_msg (day, send_day, contact_id, robot_id, uuid_id, response_json, msg, send_time,
            url, expire_date, data_msg, msg_type) 
                                  VALUES( '{day}', '{request_time}', '{contact_id}', 
                                  '{robot_id}', '{uniq_sn}', '{response_json}','{msg}','{send_time}'
                                  ,'{url}','{expire_date}','{data_msg}','{msg_type}');
                                  '''

            logger.info(insert_query)
            result = self.run_sql(insert_query, "insert")
        except Exception as ee:
            logger.error(traceback.format_exc())
        return result

    @sqlFactorReConnect
    def insert_send_browsing_history(self, customer_id, company, url, params_url, request_time):
        result = ""
        try:
            msg_con = json.dumps(params_url)
            insert_query = f'''insert into ql_work_wechat.ql_send_browsing_history (customer_id, company, url, params, request_time)  
                                  VALUES( '{customer_id}', '{company}', '{url}', '{msg_con}', '{request_time}');'''

            logger.info(insert_query)
            result = self.run_sql(insert_query, "insert")
        except Exception as ee:
            logger.error(traceback.format_exc())
        return result


if __name__ == '__main__':
    dao = EntitiesDao()
